﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;


namespace BusinessLogic
{
    public class DataConnection
    {
        string cnstr = ConfigurationManager.ConnectionStrings["QLNTConStr"].ToString();
        //string cnstr = "Data Source=THIENLE-PC\\THIENLAP; Initial Catalog=Accompro;Integrated Security=SSPI";
        public void ExecNonQuery(string query)
        {

            SqlConnection cn = new SqlConnection(cnstr);
            SqlCommand command = new SqlCommand(query, cn);
            command.Connection.Open();
            command.ExecuteNonQuery();
            command.Connection.Close();
        }

        public DataTable getData(string strsql)
        {
            SqlConnection conn = new SqlConnection(cnstr);            
            SqlDataAdapter da = new SqlDataAdapter(strsql, conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

        public bool checkCity(string cityname)
        {
            bool isValid = true;

            SqlConnection conn = new SqlConnection(cnstr);
            string strsql = "Select * from City where Name = N'" + cityname + "'";
            SqlDataAdapter da = new SqlDataAdapter(strsql, conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                isValid = false;
            }
            return isValid;
        }


        public string getCityName(int _idcity)
        {
            string strsql = "Select * from City where IdCity = '" + _idcity + "'";

            string _Name = "";

            DataTable dt = getData(strsql);
            if (dt.Rows.Count > 0)
            {
                _Name = dt.Rows[0]["Name"].ToString();
            }
            return _Name;
            
        }

        public string getDisName(int _idDis)
        {
            string strsql = "Select * from District where IdDistrict = '" + _idDis + "'";

            string _Name = "";

            DataTable dt = getData(strsql);
            if (dt.Rows.Count > 0)
            {
                _Name = dt.Rows[0]["Name"].ToString();
            }
            return _Name;

        }

        public string getCategoryName(int _idCate)
        {
            string strsql = "Select * from Category where IdCategory = '" + _idCate + "'";

            string _Name = "";

            DataTable dt = getData(strsql);
            if (dt.Rows.Count > 0)
            {
                _Name = dt.Rows[0]["Name"].ToString();
            }
            return _Name;

        }


        public bool checkDis(string Disname)
        {
            bool isValid = true;

            SqlConnection conn = new SqlConnection(cnstr);
            string strsql = "Select * from District where Name = N'" + Disname + "'";
            SqlDataAdapter da = new SqlDataAdapter(strsql, conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                isValid = false;
            }
            return isValid;
        }

        public bool checkUsername(string userName)
        {
            bool isExist = false;
            SqlConnection cn = new SqlConnection(cnstr);

            //kiểm tra username đã tồn tại chưa, nếu đã tồn tại thì return true
            string checkQuery = "select count(*) from Users where Username = '" + userName + "'";
            SqlCommand checkCmd = new SqlCommand(checkQuery, cn);
            cn.Open();
            int soLuong = (int)checkCmd.ExecuteScalar();
            cn.Close();
            if (soLuong > 0)
            {
                isExist = true;
            }
            return isExist;
        }

        public bool checkUser(string username, string password)
        {

            bool isValid = false;
            SqlConnection conn = new SqlConnection(cnstr);
            string strsql = "Select * from Users where Username='" + username + "'and Pass='" + password + "'";
            SqlDataAdapter da = new SqlDataAdapter(strsql, conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                isValid = true;
            }
            return isValid;
        }       

        public int getMaxIdentity(string sqlQuery)
        {
            SqlConnection cn = new SqlConnection(cnstr);
            cn.Open();
            SqlCommand command2 = new SqlCommand(sqlQuery, cn);
            int maUser = (int)command2.ExecuteScalar();
            cn.Close();
            return maUser;
        }

        public string getLoaiUser(string userName)
        {
            string loaiUser = "";
            string memTypeQuery = "select Membertype from Users where Username = '" + userName + "'";
            loaiUser = getStringValue(memTypeQuery);
            return loaiUser;
        }

        public int getNumberValue(string sqlQuery)
        {
            SqlConnection cn = new SqlConnection(cnstr);
            cn.Open();
            SqlCommand command2 = new SqlCommand(sqlQuery, cn);
            int value = (int)command2.ExecuteScalar();
            cn.Close();
            return value;
        }

        public string getStringValue(string sqlQuery)
        {
            SqlConnection cn = new SqlConnection(cnstr);
            cn.Open();
            SqlCommand command2 = new SqlCommand(sqlQuery, cn);
            string value = (string)command2.ExecuteScalar();
            cn.Close();
            return value;
        }

        public decimal getDecimalValue(string sqlQuery)
        {
            SqlConnection cn = new SqlConnection(cnstr);
            cn.Open();
            SqlCommand command2 = new SqlCommand(sqlQuery, cn);
            decimal value = (decimal)command2.ExecuteScalar();
            cn.Close();
            return value;
        }

        


        
    }
}
